/* QAD_StockJournal.p - Stock Journal By Part / Location / Effective Date     */
/* Copyright 1986-2010 QAD Inc., Santa Barbara, CA, USA.                      */
/* All rights reserved worldwide.  This is an unpublished work.               */
/* $Id:: QAD_TransactionsAccountingReport.p 1046 2010-09-10 09:08:19Z jvz  $: */
/*                                                                            */
/* Report matches legacy icstjrrp.p for use by QAD Reporting Framework.       */
/*                                                                            */

{mfsubdirs.i}
{{&US_BBI}mfdeclre.i}
{{&US_BBI}gplabel.i}

{com/qad/shell/report/dsReportRequest.i}
{com/qad/shell/report/ReportConstants.i}

/*============================================================================*/
/* ****************************  TEMP TABLES  ******************************* */
/*============================================================================*/

/* TEMP-TABLE DEFINITION BLOCK */

define temp-table tt_ld_det no-undo like ld_det
   field pt_desc1   like pt_mstr.pt_desc1
   field tr_um      like tr_hist.tr_um
   field opening_in like in_mstr.in_qty_oh
   field closing_in like in_mstr.in_qty_oh
   field unique_id  as   integer
   index index_ld_det ld_part ld_site ld_loc
.

define temp-table tt_tr_hist no-undo like tr_hist
   field qty_in        like tr_hist.tr_qty_loc
   field qty_out       like tr_hist.tr_qty_loc
   field endbalance    like in_mstr.in_qty_oh
   field ref_unique_id like tt_ld_det.unique_id
   index index_tr_hist tr_part tr_site tr_loc
.

define temp-table ttReportOptions no-undo
   field newpage like mfc_ctrl.mfc_logical
.

define dataset dsReportResults for tt_ld_det, tt_tr_hist, ttReportOptions.

/*============================================================================*/
/* *****************************  MAIN BLOCK  ******************************* */
/*============================================================================*/
define input parameter runReport    as logical no-undo.
define input parameter reportHandle as handle  no-undo.
define input parameter dataset      for dsReportRequest.
define output parameter dataset-handle phReportResults.

{com/qad/shell/report/reporting.i}

define variable vhDS as handle    no-undo.

empty temp-table tt_ld_det       no-error.
empty temp-table tt_tr_hist      no-error.
empty temp-table ttReportOptions no-error.

for first ttReportRequest no-lock:

   vhDS = dataset dsReportResults:handle.
   run FillMetaData in reportHandle(
      vhDS, "QAD_StockJournal.meta.xml").

   run SetReportMetaData (input reportHandle).

   if runReport then do:
      run RunReport
         (output dataset-handle phReportResults).
   end.

end.

PROCEDURE SetReportMetaData:

   define input parameter  ipReportHandle as handle.

   run SetFieldMetaParameter in ipReportHandle ("ttReportOptions",
         "newpage", "DataFormat", getLabel("YES/NO")).

END PROCEDURE. /* PROCEDURE ModifyMetaData */

FUNCTION GetValueOfOperator returns character
( input ipBufferName as character,
  input ipFieldName  as character,
  input ipOperator   as character,
  input ipDefault    as character):

   define variable retString as character no-undo.

   for first ttFilterCondition where bufferName = ipBufferName
                                 and bufferField = ipFieldName
                                 and filterOperator = ipOperator:
   end.

   if not available ttFilterCondition then do:
      retString = ipDefault.
   end.
   else do:
      retString = ttFilterCondition.filterValue.
   end.

   return retString.
END FUNCTION.

FUNCTION GetLeftValueOfRange returns character
( input ipBuffername as character,
  input ipFieldName  as character,
  input ipDefault    as character):

  return GetValueOfOperator( ipBufferName,
                              ipFieldName,
                              ">=",
                              ipDefault).
END FUNCTION.

FUNCTION GetRightValueOfRange returns character
( input ipBuffername as character,
  input ipFieldName  as character,
  input ipDefault    as character):

  return GetValueOfOperator( ipBufferName,
                              ipFieldName,
                              "<=",
                              ipDefault).
END FUNCTION.

FUNCTION GetLeftDateOfRange returns date
( input ipBuffername as character,
  input ipFieldName  as character,
  input ipDefault    as date):

  define variable retDate as date      no-undo.
  define variable strDate as character no-undo.

  assign
     strDate = GetValueOfOperator(ipBufferName,ipFieldName,">=","")
     retDate = if strDate = ""
               then 
                  ipDefault
               else
                  Date(strDate).
   return retDate.
END FUNCTION.

FUNCTION GetRightDateOfRange returns date
( input ipBuffername as character,
  input ipFieldName  as character,
  input ipDefault    as date):

  define variable retDate as date      no-undo.
  define variable strDate as character no-undo.

  assign
     strDate = GetValueOfOperator(ipBufferName,ipFieldName,"<=","")
     retDate = if strDate = ""
               then 
                  ipDefault
               else
                  Date(strDate).
   return retDate.
END FUNCTION.

/* DATA RETRIEVING LOGIC BLOCK */
PROCEDURE RunReport:

   define output parameter dataset-handle phReportResults.

   /* RETRIEVE THE DATA FROM DATABASE */
   define variable queryString   as character no-undo.
   define variable hMainQuery    as handle    no-undo.
   define query    MainQuery     for tr_hist.
   define variable newtrloc      like mfc_logical no-undo.
   define variable firstprinted  like mfc_logical no-undo.
   define variable l_item_deleted like mfc_logical no-undo.
   define variable desc1 like tt_ld_det.pt_desc1 no-undo.
   define variable qtyin      like tr_hist.tr_qty_loc no-undo.
   define variable qtyout     like tr_hist.tr_qty_loc no-undo.
   define variable effdate_left like tr_hist.tr_effdate no-undo.
   define variable effdate_right like tr_hist.tr_effdate no-undo.
   define variable endbalance  like in_mstr.in_qty_oh no-undo.
   define variable unique_id_count like tt_ld_det.unique_id initial 0 no-undo.
   define buffer trhist for tr_hist.

   assign
      hMainQuery           = query MainQuery:handle 
      effdate_right        = 
         GetRightDateOfRange( "tt_tr_hist" , "tr_effdate" , hi_date )
      effdate_left         = 
         GetLeftDateOfRange( "tt_tr_hist" , "tr_effdate" , low_date )
   .


   queryString = "for each tr_hist no-lock where tr_domain = "
                  + quoter(global_domain).

   run AddSomeConditions in reportHandle
      ("tr_hist", "tr_effdate", input-output queryString).

   queryString = queryString + " and tr_effdate <= " + string(effdate_right)
                             + " and  tr_ship_type <> 'M' "
                             + " and (tr_type <> 'RCT-PO' or "
                             +      " tr_ship_type <> 's' )"
                             + " and (tr_type <> 'ISS-PRV' or "
                             +      " tr_ship_type <> 's' )".

   queryString = queryString + " break by tr_part "
                             +       " by tr_site "
                             +       " by tr_loc "
                             +       " by tr_effdate descending "
                             +       " by tr_trnbr descending ".

   queryString = queryString + " : ".

   hMainQuery:query-prepare(queryString).
   hMainQuery:query-open().
   hMainQuery:get-next().
message queryString.

   repeat while not hMainQuery:query-off-end:

   if hMainQuery:first-of(3)
      then do:

         assign
            firstprinted     = no
            newtrloc         = true
            endbalance       = 0
            l_item_deleted   = no
         .

        /* CHECK WHETHER ITEM IS HAVING TRANSACTIONS IN GIVEN DATE RANGE */
        /* AND WHETHER IT HAS BEEN DELETED                               */

         if not can-find(first trhist no-lock
                            where trhist.tr_domain   = tr_hist.tr_domain
                            and   trhist.tr_part     = tr_hist.tr_part
                            and  (trhist.tr_effdate >= effdate_left and
                                  trhist.tr_effdate <= effdate_right)
                            and   trhist.tr_site     = tr_hist.tr_site
                            and   trhist.tr_loc      = tr_hist.tr_loc)
            and not can-find(first in_mstr no-lock
                                where in_domain = global_domain
                                and   in_part   = tr_hist.tr_part
                                and   in_site   = tr_hist.tr_site)
         then do:
            l_item_deleted = yes.
         end.

         if not l_item_deleted
         then do:

            for each ld_det
               where ld_det.ld_domain = global_domain
               and   ld_det.ld_part   = tr_hist.tr_part
               and   ld_det.ld_site   = tr_hist.tr_site
               and   ld_det.ld_loc    = tr_hist.tr_loc
            no-lock :
               endbalance = endbalance + ld_det.ld_qty_oh.
            end. /* FOR EACH ld_det */

            /* BACKOUT TRANSACTION QUANTITIES HAVING TRANSACTION */
            /* EFFECTIVE DATE GREATER THAN EFFDATE1 TO DISPLAY   */
            /* QUANTITY TILL EFFDATE1                            */

            if can-find(first trhist no-lock
                           where trhist.tr_domain   = tr_hist.tr_domain
                           and   trhist.tr_part     = tr_hist.tr_part
                           and   trhist.tr_effdate  > effdate_right
                           and   trhist.tr_site     = tr_hist.tr_site
                           and   trhist.tr_loc      = tr_hist.tr_loc)
            then do:

               for each trhist
                  where trhist.tr_domain  = tr_hist.tr_domain
                  and   trhist.tr_part    = tr_hist.tr_part
                  and   trhist.tr_effdate > effdate_right
                  and   trhist.tr_site    = tr_hist.tr_site
                  and   trhist.tr_loc     = tr_hist.tr_loc
                  and   tr_ship_type      <> "M"
                  and  (tr_type           <> "RCT-PO"
                     or tr_ship_type      <> "s")
                  and  (tr_type           <> "ISS-PRV"
                     or tr_ship_type      <> "s") 
               no-lock:

                  endbalance = endbalance - trhist.tr_qty_loc.

               end. /* FOR EACH trhist */
            end. /* CAN-FIND trhist */
         end. /* IF NOT l_item_deleted */
      end. /* FIRST-OF tr_loc */

      if     newtrloc
         and not l_item_deleted
      then do:

         desc1 = "".
         for first pt_mstr
            where pt_mstr.pt_domain = global_domain
            and   pt_mstr.pt_part   = tr_part
         no-lock:
            desc1 = pt_mstr.pt_desc1 + " " + pt_mstr.pt_desc2.
         end. /* FOR FIRST pt_mstr */

         if endbalance <> 0
            or can-find(first ld_det no-lock
                           where ld_det.ld_domain = global_domain
                           and   ld_det.ld_part   = tr_hist.tr_part
                           and   ld_det.ld_site   = tr_hist.tr_site
                           and   ld_det.ld_loc    = tr_hist.tr_loc)
            or can-find(first trhist no-lock
                           where trhist.tr_domain      = tr_hist.tr_domain
                           and   trhist.tr_part        = tr_hist.tr_part
                           and  (    trhist.tr_effdate >= effdate_left
                                 and trhist.tr_effdate <= effdate_right)
                           and   trhist.tr_site        = tr_hist.tr_site
                           and   trhist.tr_loc         = tr_hist.tr_loc
                           and   trhist.tr_qty_loc     <> 0)
         then do:
            unique_id_count = unique_id_count + 1.

            create tt_ld_det.
            buffer-copy ld_det to tt_ld_det no-error.
            assign
               tt_ld_det.unique_id = unique_id_count
               tt_ld_det.pt_desc1 = desc1
               tt_ld_det.tr_um = tr_hist.tr_um
               tt_ld_det.closing_in = endbalance
               tt_ld_det.ld_part = tr_hist.tr_part.
               tt_ld_det.ld_site = tr_hist.tr_site.
               tt_ld_det.ld_loc  = tr_hist.tr_loc.
            .

            firstprinted = yes.
         end. /* IF CAN-FIND */
      end. /* IF newtrloc and .. */

      newtrloc = false.

      assign
         qtyin  = 0
         qtyout = 0.

      /* DISPLAY TRANSACTIONS WITHIN DATE RANGE */

      if     tr_hist.tr_effdate >= effdate_left
         and tr_hist.tr_qty_loc <> 0.0
      then do:

         if     not (tr_hist.tr_type      = "RCT-PO" and
                     tr_hist.tr_ship_type = "s")
            and not (tr_hist.tr_type      = "ISS-PRV" and
                     tr_hist.tr_ship_type = "s")
         then do:
            if tr_hist.tr_qty_loc >= 0
            then
               qtyin  = tr_hist.tr_qty_loc.
            if tr_hist.tr_qty_loc <  0
            then
               qtyout = tr_hist.tr_qty_loc.
         end. /*IF NOT(tr_type = "RCT-PO" ... */

         if qtyin <> 0 or qtyout <> 0
         then do:

            if firstprinted
            then do:
               create tt_tr_hist.
               buffer-copy tr_hist to tt_tr_hist no-error.
               assign
                  tt_tr_hist.ref_unique_id = unique_id_count
                  tt_tr_hist.qty_in     = qtyin
                  tt_tr_hist.qty_out    = qtyout
                  tt_tr_hist.endbalance = endbalance
                  tt_tr_hist.tr_effdate = tr_hist.tr_effdate
                  tt_tr_hist.tr_date    = tr_hist.tr_date
                  tt_tr_hist.tr_type    = tr_hist.tr_type
                  tt_tr_hist.tr_trnbr   = tr_hist.tr_trnbr
                  tt_tr_hist.tr_addr    = tr_hist.tr_addr
                  tt_tr_hist.tr_ship_id = tr_hist.tr_ship_id
               .
            end. /* IF firstprinted */
         end. /* IF qtyin <> 0.0 or ... */

         /* COMPUTE THE BEGINNING BALANCE */
         if     not (tr_hist.tr_type      = "RCT-PO" and
                     tr_hist.tr_ship_type = "s" )
            and not (tr_hist.tr_type      = "ISS-PRV" and
                     tr_hist.tr_ship_type = "s")
         then
            endbalance = endbalance - tr_hist.tr_qty_loc.
      end. /* IF tr_effdate >= effdate ... */

      if hMainQuery:last-of(3)
         and not l_item_deleted
         and firstprinted
      then do:
         /* DISPLAY INITIAL BALANCE */
         firstprinted = no.
         tt_ld_det.opening_in = endbalance.      
      end. /* IF LAST-OF(tr_loc)... */

      hMainQuery:get-next().
   end. /* REPEAT QUERY */

   hMainQuery:query-close().
   delete object hMainQuery.
   phReportResults = dataset dsReportResults:handle.

END PROCEDURE.
